package source;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.LinkedList;


public class PlayerDataAccess {

	Connection connection;


	public PlayerDataAccess() {

		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost/poliholdem";
			connection = DriverManager.getConnection(url, "phm", "phm");      
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}


	public boolean updatePlayer(int id_pl, String nome, String cogn, double punti_class,
			int tornei_partecipati, double guadagno) throws SQLException{

		ArrayList<Player> result = searchId(id_pl);

		if(result.size()!=1){
			System.out.println("ERROR in updatePlayer()! Id "+id_pl+" presente in più instanze");
			return false;
		}
		
		PreparedStatement stm = connection.prepareStatement("UPDATE player"+
		" SET nome=?, cognome=?, punti_classifica=?, tornei_partecipati=?, guadagno=? WHERE id_player=?");
		
		stm.setString(1,nome);
		stm.setString(2,cogn);
		stm.setDouble(3,punti_class);
		stm.setInt(4,tornei_partecipati);
		stm.setDouble(5,guadagno);
		stm.setInt(6,id_pl);

		return !stm.execute();
	}

	
	public boolean insertNewPlayer(int id_pl, String nome, String cogn, double punti_class,
			int tornei_partecipati, double guadagno) throws SQLException{

		//check if id exists or not
		if (searchOmonimo(nome, cogn).isEmpty())
			System.out.println("ALERT! Verrà inserito un omonimo nel database.");

		if (existIdPlayer(id_pl)){
			System.out.println("ERROR! Id del nuovo player già esistente.");
			return false;
		}

		PreparedStatement stm = connection.prepareStatement("INSERT INTO player"+
		" VALUES(?, ?, ?, ?, ?, ?)");

		stm.setInt(1,id_pl);
		stm.setString(2,nome);
		stm.setString(3,cogn);
		stm.setDouble(4,punti_class);
		stm.setInt(5,tornei_partecipati);
		stm.setDouble(6,guadagno);

		return !stm.execute();
	}
	
	
	public boolean deletePlayer(int id_pl) throws SQLException {
		
		PreparedStatement stm = connection.prepareStatement("DELETE * FROM player"+
		" WHERE id_player=?");
		stm.setInt(1, id_pl);
		
		return !stm.execute();
	}


	public boolean existIdPlayer(int id_pl) throws SQLException {

		ArrayList<Player> result = new ArrayList<Player>();
		
		PreparedStatement stm = connection.prepareStatement("SELECT * FROM player"+
		" WHERE id_player=?");
		stm.setInt(1, id_pl);
		ResultSet rs = stm.executeQuery();

		while (rs.next()) {
			Player player = new Player(rs.getInt("id_player"), rs.getString("nome"), rs.getString("cognome"),
					rs.getDouble("punti_classifica"), rs.getInt("tornei_partecipati"),
					rs.getDouble("guadadgno"),rs.getInt("posizione"));
			result.add(player);
		}

		if(result.isEmpty()){

			System.out.println("No player found with id="+id_pl);
			return false;
		}
		if(result.size()>1){
			System.out.println("ERROR! More than one player found with id="+id_pl);
			return false;
		}

		return true;

	}


	/**Omonimo =  name + surname*
	 * @throws SQLException */
	public Collection<Player> searchOmonimo(String nome, String cogn) throws SQLException {

		Collection<Player> result = new LinkedList<Player>();

		PreparedStatement stm = connection.prepareStatement("SELECT * FROM player"+
		" WHERE nome=? AND cognome=?");
		stm.setString(1, nome);
		stm.setString(2, cogn);
		ResultSet rs = stm.executeQuery();

		while (rs.next()) {
			Player player = new Player(rs.getInt("id_player"), rs.getString("nome"), rs.getString("cognome"),
					rs.getDouble("punti_classifica"), rs.getInt("tornei_partecipati"),
					rs.getDouble("guadagno"),rs.getInt("posizione"));
			result.add(player);
		}

		return result;
	}


	public ArrayList<Player> searchId(int id) throws SQLException {

		ArrayList<Player> result = new ArrayList<Player>();
		PreparedStatement stm = connection.prepareStatement("SELECT * FROM player"+
		" WHERE id_player=?");
		stm.setInt(1, id);
		ResultSet rs = stm.executeQuery();

		while (rs.next()) {
			Player player = new Player(rs.getInt("id_player"), rs.getString("nome"), rs.getString("cognome"),
					rs.getDouble("punti_classifica"), rs.getInt("tornei_partecipati"),
					rs.getDouble("guadagno"),rs.getInt("posizione"));
			result.add(player);
		}

		return result;
	}
	
	
	public ArrayList<Player> getPlayersOrdered() throws SQLException {

		ArrayList<Player> result = new ArrayList<Player>();
		PreparedStatement stm = connection.prepareStatement("SELECT * FROM player"+
		" ORDER BY punti_classifica DESC, tornei_partecipati DESC, guadagno DESC");
		ResultSet rs = stm.executeQuery();

		while (rs.next()) {
			Player player = new Player(rs.getInt("id_player"), rs.getString("nome"), rs.getString("cognome"),
					rs.getDouble("punti_classifica"), rs.getInt("tornei_partecipati"),
					rs.getDouble("guadagno"),rs.getInt("posizione"));
			result.add(player);
		}

		return result;
	}
	
	
	public int getNumTotPlayer() throws SQLException {
		
		int n=0;
		PreparedStatement stm = connection.prepareStatement("SELECT count(id_player) FROM player");
		ResultSet rs = stm.executeQuery();
		
		while (rs.next()){
			n = rs.getInt("count(id_player)");
		}
		return n;	
		
	}
}

